package cn.ffcs.uec.view.component;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.zkoss.web.servlet.Servlets;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Components;
import org.zkoss.zk.ui.Executions;
import org.zkoss.zk.ui.IdSpace;
import org.zkoss.zk.ui.SuspendNotAllowedException;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zkplus.databind.BindingListModelList;
import org.zkoss.zul.Div;
import org.zkoss.zul.Filedownload;
import org.zkoss.zul.ListModel;
import org.zkoss.zul.Messagebox;
import org.zkoss.zul.Window;

import com.alibaba.fastjson.JSONArray;
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPRow;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

import cn.ffcs.raptornuke.portal.kernel.xml.DocumentException;
import cn.ffcs.raptornuke.portal.theme.ThemeDisplay;
import cn.ffcs.uec.outStaff.model.UecOutStaffInfo;
import cn.ffcs.uec.staff.manager.UecAssManager;
import cn.ffcs.uec.staff.manager.UecDocEduManager;
import cn.ffcs.uec.staff.manager.UecEncManager;
import cn.ffcs.uec.staff.manager.UecFamilyManager;
import cn.ffcs.uec.staff.manager.UecNationdutyManager;
import cn.ffcs.uec.staff.manager.UecTitleManager;
import cn.ffcs.uec.staff.manager.UecWorkManager;
import cn.ffcs.uec.staff.model.UecAss;
import cn.ffcs.uec.staff.model.UecDocEdu;
import cn.ffcs.uec.staff.model.UecEnc;
import cn.ffcs.uec.staff.model.UecFamily;
import cn.ffcs.uec.staff.model.UecNationduty;
import cn.ffcs.uec.staff.model.UecTalentTeam;
import cn.ffcs.uec.staff.model.UecTitle;
import cn.ffcs.uec.staff.model.UecWork;
import cn.ffcs.uec.staffAndOutStaff.manager.StaffAndOutStaffManager;
import cn.ffcs.uec.staffAndOutStaff.vo.StaffAndOutStaff;
import cn.ffcs.uec.view.component.bean.UecStaffAndOutStaffListAssboxExtBean;
import cn.ffcs.uec.view.component.bean.UecStaffAndOutStaffListboxExtBean;
import cn.ffcs.uec.view.manager.UecStaffAllManager;
import cn.ffcs.uec.view.model.UecArticle;
import cn.ffcs.uec.view.model.UecImportantWork;
import cn.ffcs.uec.view.model.UecInnovation;
import cn.ffcs.uec.view.model.UecStaffAll;
import cn.ffcs.uec.view.util.ExcelUtil;
import cn.ffcs.uom.common.util.ApplicationContextUtil;
import cn.ffcs.uom.common.util.IPortletInfoProvider;
import cn.ffcs.uom.common.util.ListboxUtils;
import cn.ffcs.uom.common.util.PubUtil;
import cn.ffcs.uom.common.util.StrUtil;
import cn.ffcs.uom.common.util.UomZkUtil;
import cn.ffcs.uom.common.vo.NodeVo;
import cn.ffcs.uom.common.vo.PageInfo;
import lombok.Getter;
import lombok.Setter;

/**
 * 人员信息，双击下载考核表格
 * 
 * @Title:
 * @Description:
 * @author BMP
 * @date 2019年11月4日
 */
@Controller
@Scope("prototype")
public class UecStaffAndOutStaffListAssboxExt extends Div implements IdSpace {

    private static final long serialVersionUID = 7004119232729703597L;

    private static final String UEC_STAFF_AND_OUTSTAFF_ZUL = "/pages_nmg/views/allStaffAssCenter/comp/uecStaffAndOutStaff_listbox_ext.zul";

    private UecStaffAndOutStaffListAssboxExtBean bean = new UecStaffAndOutStaffListAssboxExtBean();

   //private StaffAndOutStaff staffAndOutStaff = new StaffAndOutStaff();
    private UecStaffAll uecStaffAll = new UecStaffAll();

    private UecStaffAllManager uecStaffAllManager = (UecStaffAllManager) ApplicationContextUtil
            .getBean("uecStaffAllManager");

    List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
    List uecStaffAllList=null;
    private UecAssManager uecAssManager = (UecAssManager) ApplicationContextUtil
			.getBean("uecAssManager");

	List<UecAss> findUecAssByPkPsndoc=null;
	int temp=0;
	int temp1=0;
	//定义列表行，为了公用
	HSSFRow row=null;
	//定义列表行单元格，为了公用
	HSSFCell beginCell=null;
    @Getter
    @Setter
    private IPortletInfoProvider portletInfoProvider;

    public UecStaffAndOutStaffListAssboxExt() {
        Executions.createComponents(UEC_STAFF_AND_OUTSTAFF_ZUL, this, null);
        Components.wireVariables(this, bean);
        Components.addForwards(this, this, '$');
    }

    /**
     * 界面初始化
     */
    public void onCreate() {
    	onQueryStaffAll();
    	// 组织名称
		List<NodeVo> nodeVoIshiskeypsnList = new ArrayList<NodeVo>();
		String[][] ishiskeypsn = { { "阿拉善分公司*", "阿拉善分公司*" }, { "巴彦淖尔分公司*", "巴彦淖尔分公司*" }, { "包头分公司*", "包头分公司*" },
		 { "赤峰分公司*", "赤峰分公司*" }, { "鄂尔多斯分公司*", "鄂尔多斯分公司*" }, { "呼和浩特分公司*", "呼和浩特分公司*" }, { "呼伦贝尔分公司*", "呼伦贝尔分公司*" },
		 { "内蒙古分公司*", "内蒙古分公司*" }, { "通辽分公司*", "辽分公司*" }, { "乌海分公司*", "乌海分公司*" }, { "乌兰察布分公司*", "乌兰察布分公司*" },
		 { "锡林郭勒分公司*", "锡林郭勒分公司*" }, { "兴安盟分公司*", "兴安盟分公司*" }};
		for (int i = 0; i < ishiskeypsn.length; i++) {
			NodeVo nodeVo = new NodeVo();
			for (int j = 0; j < 1; j++) {
				nodeVo.setId(ishiskeypsn[i][j]);
				nodeVo.setName(ishiskeypsn[i][j + 1]);
			}
			nodeVoIshiskeypsnList.add(nodeVo);
		}
		ListboxUtils.rendererForEdit(bean.getOrgList(), nodeVoIshiskeypsnList);
    }

    /**
     * 双击选中事件
     * @throws InterruptedException 
     * @throws SuspendNotAllowedException 
     */
    public void onStaffAndOutStaffSelectRequest() throws SuspendNotAllowedException, InterruptedException {
        if (this.bean.getUecStaffAndOutStaffListboxs().getSelectedCount() > 0) {
        	uecStaffAll = (UecStaffAll) this.bean.getUecStaffAndOutStaffListboxs().getSelectedItem().getValue();
            try {
            	//this.binbBean();
				this.onOk();
				uecStaffAll=null;
				this.onQueryStaffAll();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    			
        }
    }

    /**
     * 查询员工信息
     * 
     * @throws Exception
     *             异常
     */
    public void onQueryStaffAll() {
    	if (!StrUtil.isNullOrEmpty(this.bean.getStaffCode().getValue())) {
    		uecStaffAll.setCode(this.bean.getStaffCode().getValue());
		}
		if (!StrUtil.isNullOrEmpty(this.bean.getStaffName().getValue())) {
			uecStaffAll.setName(this.bean.getStaffName().getValue());
		}
		if(this.bean.getOrgList().getSelectedItem()!= null) {
			if (!StrUtil.isNullOrEmpty(this.bean.getOrgList().getSelectedItem().getValue())) {
				uecStaffAll.setForgname((String)this.bean.getOrgList().getSelectedItem().getValue());
			}
		}
        int activePage = bean.getUecStaffAndOutStaffListboxPaging().getActivePage();
        int pageSize = bean.getUecStaffAndOutStaffListboxPaging().getPageSize();
        PageInfo pageInfo = uecStaffAllManager.queryPageInfoByUecStaffAll(uecStaffAll, activePage+1,
        		pageSize);
        // 总条数
        if(pageInfo !=null) {
	        bean.getTotalSize().setValue(String.valueOf(pageInfo.getTotalCount()));
	        ListModel list = new BindingListModelList(pageInfo.getDataList(), true);
	        uecStaffAllList=pageInfo.getDataList();
	        this.bean.getUecStaffAndOutStaffListboxs().setModel(list);
	        this.bean.getUecStaffAndOutStaffListboxPaging().setTotalSize(pageInfo.getTotalCount());
        }
        
    }

    /**
     * 每页显示事件
     */
    public void onPageSizeSelect() {
        if (this.bean.getPageListbox() != null && this.bean.getPageListbox().getSelectedItem().getValue() != null) {
            this.bean.getUecStaffAndOutStaffListboxPaging().setActivePage(0);
            this.bean.getUecStaffAndOutStaffListboxPaging()
                    .setPageSize(Integer.parseInt(this.bean.getPageListbox().getSelectedItem().getValue().toString()));
        }
        this.onQueryStaffAll();
    }

    /**
     * 翻页
     */
    public void onStaffAndOutStaffListboxPaging() {
    	onQueryStaffAll();
    }

    /**
     * 重置按钮
     */
    public void onReset() {
        //clearVariable();
        this.bean.getStaffCode().setValue(null);
        this.bean.getStaffName().setValue(null);
        this.bean.getStaffName().setValue(null);
        this.bean.getTotalSize().setValue(null);
        this.bean.getOrgList().clearSelection();
        ListboxUtils.clearListbox(this.bean.getUecStaffAndOutStaffListboxs());
        this.bean.getUecStaffAndOutStaffListboxPaging().setTotalSize(1);
        uecStaffAll = new UecStaffAll();
        onQueryStaffAll();
    }


    /**
     * 清除变量
     */
    public void clearVariable() {
    	uecStaffAll = null;
    }

	/**
	 * @throws Exception 
	 * @throws ParseException 
	 * @throws InterruptedException
	 *             员工报名表导出按钮，通过按钮触发
	 * @Title: onOk
	 * @Description: TODO
	 * @param:
	 * @return: void
	 * @throws
	 */
	public void onOk() throws Exception {
		try {
			String charset = "UTF-8";
            // 图片名称
            String fileName = uecStaffAll.getName()+"考核档案";
            // 编码后文件名
            String encodedName = null;
            encodedName = URLEncoder.encode(fileName, charset);
            byte[] bsPdf = exportExcel();
            // 将空格替换为+号
            encodedName = encodedName.replace("%20", "+");
            HttpServletRequest httpRequest = (HttpServletRequest) Executions.getCurrent()
                    .getNativeRequest();
            // 解决ie6 bug 或者是火狐浏览器
            if (encodedName.length() > 150 || Servlets.isGecko(httpRequest)
                    || Servlets.isGecko3(httpRequest)) {
                encodedName = new String(fileName.getBytes(charset), "ISO8859-1");
            }
			Filedownload.save(bsPdf, "application/vnd.ms-excel", fileName);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @throws com.itextpdf.text.DocumentException
	 * @desc 生成pdf
	 * @return: byte[] pdf二进制文件
	 * @throws DocumentException
	 * @throws IOException
	 * @throws com.itextpdf.text.DocumentException
	 * @throws ParseException 
	 * @throws InterruptedException
	 */
	public byte[] exportExcel() throws IOException{
		if(uecStaffAll != null ) {
			StaffAndOutStaff	staffAndOutStaff = new StaffAndOutStaff();
			staffAndOutStaff.setPkPsndoc(uecStaffAll.getPkpsndoc());
			findUecAssByPkPsndoc = uecAssManager.findUecAssByPkPsndoc(staffAndOutStaff);
		}
				HSSFWorkbook workbook = new HSSFWorkbook();
			    HSSFSheet sheet = workbook.createSheet("员工考核信息表");
			    //单元格标题格式(开始)
			    HSSFCellStyle cellStyle1 = workbook.createCellStyle();  //单元格标题
			    cellStyle1.setAlignment(HorizontalAlignment.CENTER);  
			    cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
			    cellStyle1.setWrapText(true);//自动换行
			    cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
			    cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
			    cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			    cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			    HSSFFont fonttitle = workbook.createFont();  
			    fonttitle.setColor(HSSFColor.BLACK.index);  
			    fonttitle.setBold(true);
			    fonttitle.setFontHeightInPoints((short) 16);;
			    cellStyle1.setFont(fonttitle);
			    //单元格标题格式（结束）
			  //单元格正文格式(开始)
			    HSSFCellStyle cellStyle = workbook.createCellStyle();  //单元格标题
			    cellStyle.setAlignment(HorizontalAlignment.CENTER);  
			    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			    cellStyle.setWrapText(true);//自动换行
			     HSSFFont font = workbook.createFont();  
			    font.setColor(HSSFColor.BLACK.index);  
			    font.setBold(true);
			    //font.setFontHeight((short)3);
			    cellStyle.setFont(font);
			    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
		        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			  //单元格正文格式（结束）
		        
		        //单元格正文内容格式(开始)
		        HSSFCellStyle cellStyle2 = workbook.createCellStyle();  //单元格标题
		        cellStyle2.setAlignment(HorizontalAlignment.CENTER);  
		        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
		        cellStyle2.setWrapText(true);//自动换行
		        HSSFFont font2 = workbook.createFont();  
		        font2.setColor(HSSFColor.BLACK.index);  
		        cellStyle2.setFont(font2);
		        cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
		        cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		        cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		        cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		        //单元格正文格式（结束）
			    // 将第一行的8个单元格给合并
			   // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
			    HSSFRow row = sheet.createRow(0);
			    //row.setHeight((short)18);
			    HSSFCell beginCell = row.createCell(0);
			    beginCell.setCellValue("员工考核信息表");  
			    row.createCell(1).setCellValue("");
			    row.createCell(2).setCellValue("");
		        row.createCell(3).setCellValue("");
		        row.createCell(4).setCellValue("");
		        row.createCell(5).setCellValue("");
		        row.createCell(6).setCellValue("");
		        row.createCell(7).setCellValue("");
		       
		       
		     // 将第一行的8个单元格给合并
			   // beginCell.setCellStyle(cellStyle1);
		        setBorderStyle(row,cellStyle1);
			    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
			    setRegionStyle(sheet,new CellRangeAddress(0, 0, 0, 7),cellStyle1);
			    row = sheet.createRow(1);
			    beginCell= row.createCell(0);
			    beginCell.setCellValue("员工编码"); 
			    beginCell.setCellStyle(cellStyle);
			    beginCell= row.createCell(2);
			    beginCell.setCellValue("员工姓名"); 
			    beginCell.setCellStyle(cellStyle);
			    beginCell= row.createCell(4);
			    beginCell.setCellValue("性别"); 
			    beginCell.setCellStyle(cellStyle);
			    beginCell= row.createCell(6);
			    beginCell.setCellValue("联系方式"); 
			    beginCell.setCellStyle(cellStyle);
			        if(uecStaffAll !=null) {
			         row.createCell(1).setCellValue(isNullString(uecStaffAll.getCode())); 
			         row.createCell(3).setCellValue(isNullString(uecStaffAll.getName())); 
			         row.createCell(5).setCellValue(isNullString(uecStaffAll.getSex())); 
			         HSSFCell createCell = row.createCell(7);
			         createCell.setCellValue(isNullString(uecStaffAll.getMobile()));  
			         createCell.setCellStyle(cellStyle2);
			        }else {
				         row.createCell(1).setCellValue("");  
					     row.createCell(3).setCellValue(""); 
					     row.createCell(5).setCellValue(""); 
					     row.createCell(7).setCellValue(""); 
			        }
			        setBorderStyle(row,cellStyle2);
			    row = sheet.createRow(2);  
			    beginCell= row.createCell(0);
			    beginCell.setCellValue("现工作单位");
			    row.createCell(1).setCellValue(isNullString(uecStaffAll.getOrgname()));
			    row.createCell(2).setCellValue("");
			    row.createCell(3).setCellValue("");
			    beginCell.setCellStyle(cellStyle1);
			    sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 3));
			    beginCell=row.createCell(4);
			    row.createCell(4).setCellValue("现工作部门"); 
			    row.createCell(5).setCellValue(isNullString(uecStaffAll.getDeptname())); 
			    row.createCell(6).setCellValue(""); 
			    row.createCell(7).setCellValue(""); 
			    beginCell.setCellStyle(cellStyle);
			    setBorderStyle(row,cellStyle2);
			  	  setRegionStyle(sheet,new CellRangeAddress(2, 2, 5, 7),cellStyle2);

			    sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7));
			    //考核信息加载
			    int countrow=3;
			      String[]  strList6= {"考核信息","考核类型","","考核等级","考核单位","","",""};
				   getTitle(strList6,sheet,countrow,cellStyle); 
				  sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 1,2));
			      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 4,7));
			      countrow=countrow+1;
			    int tempD=0;
			    UecAss uecass = new UecAss();
			    if(findUecAssByPkPsndoc != null && findUecAssByPkPsndoc.size()>0) {
			    	tempD=findUecAssByPkPsndoc.size();
			    	if(tempD>=3) {
			    		for (int i = 0; i < tempD; i++) {
			    			uecass=findUecAssByPkPsndoc.get(i);
			    			 row = sheet.createRow(countrow+i);
			    			 row.createCell(0).setCellValue("");    
		    			  //   row.createCell(1).setCellValue(isNullString(uecass.getBegindate()));    
		    			     row.createCell(1).setCellValue(!StrUtil.isNullOrEmpty(uecass.getBegindate())?uecass.getBegindate().substring(0,4)+"年度":"");    
		    			    // row.createCell(2).setCellValue(isNullString(uecass.getEnddate())); 
		    			     row.createCell(2).setCellValue("");
		    			     row.createCell(3).setCellValue(isNullString(uecass.getPkPerankName()));    
		    			     row.createCell(4).setCellValue(isNullString(uecass.getPkOrgassName()));    
		    			     row.createCell(5).setCellValue("");    
		    			     row.createCell(6).setCellValue("");    
		    			     row.createCell(7).setCellValue(""); 
		    			     setBorderStyle(row,cellStyle2);
		    			     sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 1,2));
		    			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

		    			    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 4,7));
						}
			    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+tempD-1, 0,0));
			    		 countrow=countrow+tempD;
			    	}else {
			    		for (int i = 0; i < tempD; i++) {
			    			uecass=findUecAssByPkPsndoc.get(i);
			    			 row = sheet.createRow(countrow+i);
			    			 row.createCell(0).setCellValue("");    
		    			     row.createCell(1).setCellValue(!StrUtil.isNullOrEmpty(uecass.getBegindate())?uecass.getBegindate().substring(0,4)+"年度":"");    
		    			    // row.createCell(2).setCellValue(isNullString(uecass.getEnddate()));    
		    			     row.createCell(2).setCellValue("");
		    			     row.createCell(3).setCellValue(isNullString(uecass.getPkPerankName()));    
		    			     row.createCell(4).setCellValue(isNullString(uecass.getPkOrgassName()));    
		    			     row.createCell(5).setCellValue("");    
		    			     row.createCell(6).setCellValue("");    
		    			     row.createCell(7).setCellValue(""); 
		    			     setBorderStyle(row,cellStyle2);
		    			     sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 1,2));
		    			    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 4,7));
		  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

						}
			    		for (int j = 0; j < 3-tempD; j++) {
			    			 row = sheet.createRow(countrow+tempD+j);
			    			 row.createCell(0).setCellValue("");    
		    			     row.createCell(1).setCellValue("");    
		    			     row.createCell(2).setCellValue("");    
		    			     row.createCell(3).setCellValue("");    
		    			     row.createCell(4).setCellValue("");    
		    			     row.createCell(5).setCellValue("");    
		    			     row.createCell(6).setCellValue("");    
		    			     row.createCell(7).setCellValue(""); 
		    			     setBorderStyle(row,cellStyle2);
		    			     sheet.addMergedRegion(new CellRangeAddress(countrow+tempD+j, countrow+tempD+j, 1,2));

		    			    sheet.addMergedRegion(new CellRangeAddress(countrow+tempD+j, countrow+tempD+j, 4,7));
		  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+tempD+j,countrow+tempD+j, 4, 7),cellStyle2);

			    		}
			    		sheet.addMergedRegion(new CellRangeAddress(countrow, countrow+2, 0,0));
			    		 countrow=countrow+3;
			    	}
			    }
			    else {
			    	for (int j = 0; j < 3; j++) {
		   			 row = sheet.createRow(countrow+j);
		   			 row.createCell(0).setCellValue("");    
					     row.createCell(1).setCellValue("");    
					     row.createCell(2).setCellValue("");    
					     row.createCell(3).setCellValue("");    
					     row.createCell(4).setCellValue("");    
					     row.createCell(5).setCellValue("");    
					     row.createCell(6).setCellValue("");    
					     row.createCell(7).setCellValue(""); 
					     setBorderStyle(row,cellStyle2);
						 sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 1,2));
					    sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 4,7));
					  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 4, 7),cellStyle2);

					}
		   		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+2, 0,0));
		   		countrow = countrow+3;
			    }
			    setBorderStyle(row,cellStyle2);
			    //单元格标题格式(开始)
			    HSSFCellStyle cellStyletemp = workbook.createCellStyle();  //末尾单元格
			    cellStyletemp.setAlignment(HorizontalAlignment.CENTER);  
			    cellStyletemp.setVerticalAlignment(VerticalAlignment.BOTTOM);
			    cellStyletemp.setBorderBottom((short)1000);
			    cellStyletemp.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
			    cellStyletemp.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
			    cellStyletemp.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			    cellStyletemp.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			    row = sheet.createRow(countrow);
			    HSSFCell createCell = row.createCell(0);
			    createCell.setCellValue("所在单位(盖章) "); 
			    createCell.setCellStyle(cellStyletemp);
			    row.createCell(1).setCellValue("");  
			    row.createCell(2).setCellValue("");  
			    row.createCell(3).setCellValue("");  
			    row.createCell(4).setCellValue("");  
			    row.createCell(5).setCellValue("");  
			    row.createCell(6).setCellValue("");  
			    row.createCell(7).setCellValue("");  
			    row = sheet.createRow(countrow+1);
			    row.createCell(0).setCellValue("");  
			    row.createCell(1).setCellValue("");  
			    row.createCell(2).setCellValue("");  
			    row.createCell(3).setCellValue("");  
			    row.createCell(4).setCellValue("");  
			    row.createCell(5).setCellValue("");  
			    row.createCell(6).setCellValue("");  
			    row.createCell(7).setCellValue(""); 
			    row = sheet.createRow(countrow+2);
			    row.createCell(0).setCellValue("");  
			    row.createCell(1).setCellValue("");  
			    row.createCell(2).setCellValue("");  
			    row.createCell(3).setCellValue("");  
			    row.createCell(4).setCellValue("");  
			    row.createCell(5).setCellValue("");  
			    row.createCell(6).setCellValue("");  
			    row.createCell(7).setCellValue(""); 
			    sheet.addMergedRegion(new CellRangeAddress(countrow, countrow+2, 0,7));
			    setRegionStyle(sheet,new CellRangeAddress(countrow,countrow+2, 0, 7),cellStyletemp);
			    /**
				 * 通过浏览器下载
				 */
			    sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
			    sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
			    sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.01 ); // 左边距
			    sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.01 ); // 右边距
			    int rowfirst=sheet.getFirstRowNum();
			    int lastfirst=sheet.getLastRowNum();
			    HSSFRow row1=sheet.getRow(rowfirst);
			    row1.setHeight((short)500);
			    //System.out.println("rowfirst======="+rowfirst+"----lastfirst----"+lastfirst);
			    for(int i=0;i<lastfirst-rowfirst+1;i++) {
			    	row1=sheet.getRow(i);
				    row1.setHeight((short)500);
			    }
			   ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			    workbook.write(byteArrayOutputStream);
			  //将流写进response输出流中
				if (byteArrayOutputStream != null) {
					byte[] bs = byteArrayOutputStream.toByteArray();
					byteArrayOutputStream.close();
					return bs;
				}
				return null;

				/* FileOutputStream fout = new FileOutputStream("C:/Users/BMP/Desktop/Test.xls");
			    workbook.write(fout);*/
			
	}
   
	 public String dataTraslate(String data) throws ParseException  {
	    	SimpleDateFormat df=new SimpleDateFormat("yyyy-mm-dd");
	    	Date parse = df.parse(data);
	    	String newDate = df.format(parse);
	    	return newDate;
	    }
	 //是否为空，不为空返回原值，否则返回" "
	 public String isNullString(String data){
		   String datanew =StrUtil.isNullOrEmpty(data)?"":data;
		 return datanew;
	 }
	/**
	 * 给标题单元格设置格式
	 * @param strList   单元格内容，字符串数组
	 * @param sheet     
	 * @param rowIndex   定义行索引，从0开始，需要手工输入
	 * @param cellStyle  单元格样式
	 * @return
	 */
	public HSSFCell getTitle(String[] strList,HSSFSheet sheet,int rowIndex,HSSFCellStyle cellStyle) {
		if(strList != null && strList.length>0) {
			beginCell=null;
			 row = sheet.createRow(rowIndex);
			for (int i = 0; i < strList.length; i++) {
		    	beginCell = row.createCell(i);
		    	beginCell.setCellValue(strList[i]);
		    	beginCell.setCellStyle(cellStyle);
			}
		}
		return beginCell;
	}
	
	/**
	 * 给标题单元格设置格式
	 * @param strList   单元格内容，字符串数组
	 * @param sheet     
	 * @param rowIndex   定义行索引，从0开始，需要手工输入
	 * @param cellStyle  单元格样式
	 * @return
	 */
	public void setBorderStyle(HSSFRow row,HSSFCellStyle cellStyle) {
	      row.getCell(0).setCellStyle(cellStyle);
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum()-1; i++) {
				 row.getCell(i).setCellStyle(cellStyle);
				 
		}
		//return row;
	}
	public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region,
            HSSFCellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            HSSFCell cell = null;
            //循环设置单元格样式
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
        
    }
	//单元格格式
	public HSSFCell getStyle(HSSFCell beginCell,HSSFCellStyle cellStyle ) {
		    	beginCell.setCellStyle(cellStyle);
		return beginCell;
	}
	//批量下载
	@SuppressWarnings("deprecation")
	public void onGetAllDown() throws Exception {
		if(uecStaffAllList != null && uecStaffAllList.size()>0) {
			int a=0;
			for (int i = 0; i < uecStaffAllList.size(); i++) {
				//uecStaffAll=(UecStaffAll)uecStaffAllList.get(i);
				onOk();
				a++;
			}
			/*	OutputStream outXlsx = new FileOutputStream("E://test.zip");
		        ZipOutputStream zipOutputStream = new ZipOutputStream(outXlsx);
		        UecStaffAndOutStaffListAssboxExt aa=new UecStaffAndOutStaffListAssboxExt();
		        //for(int i=1;i<6;i++) {
		        Date ab= new Date();
		         int seconds1 = ab.getSeconds();
		        for (int i = 0; i < uecStaffAllList.size(); i++) {
					uecStaffAll=(UecStaffAll)uecStaffAllList.get(i);
		        	//String dir = i%2==0?"dirA":"dirB";
		        	String dir="dirA";
		            ByteArrayOutputStream baos = new ByteArrayOutputStream();
		            byte[] exportExcel = aa.exportExcel();
		           // ExcelUtil.compressFileToZipStream(zipOutputStream, baos, dir+"/test"+i+".xlsx");
		            //ExcelUtil.compressFileToZipStream(zipOutputStream, baos, dir+"/"+uecStaffAll.getName()+".xlsx");
		            compressFileToZipStream(zipOutputStream, baos,exportExcel, dir+"/"+uecStaffAll.getName()+".xlsx");
		            baos.close();
		        }       
		        zipOutputStream.flush();
		        zipOutputStream.close();
		        outXlsx.close();
		        Date ab2= new Date();
		         int seconds2 = ab2.getSeconds();
		        System.out.println("导出zip完成");
		        System.out.println("总耗时（s）-----"+(seconds2-seconds1))*/;
			//System.out.println("下载次数----------"+a);
		}else {
			Messagebox.show("人员信息为空");
		}
	}
	
	
	
	  /**
		 *  压缩单个excel文件的输出流 到zip输出流,注意zipOutputStream未关闭，需要交由调用者关闭之
		 * @param zipOutputStream zip文件的输出流
		 * @param excelOutputStream excel文件的输出流
		 * @param excelFilename 文件名可以带目录，例如 TestDir/test1.xlsx
		 */
		public static void compressFileToZipStream(ZipOutputStream zipOutputStream, 
				ByteArrayOutputStream excelOutputStream,byte[] content,String excelFilename) {
			byte[] buf = new byte[1024];
			try {
				// Compress the files
				//byte[] content = excelOutputStream.toByteArray();
				ByteArrayInputStream is = new ByteArrayInputStream(content);
				BufferedInputStream bis = new BufferedInputStream(is);
				// Add ZIP entry to output stream.
				zipOutputStream.putNextEntry(new ZipEntry(excelFilename));
				// Transfer bytes from the file to the ZIP file
				int len;
				while ((len = bis.read(buf)) > 0) {
					zipOutputStream.write(buf, 0, len);
				}
				// Complete the entry
				//excelOutputStream.close();//关闭excel输出流
				zipOutputStream.closeEntry();
				bis.close();
				is.close();
				// Complete the ZIP file
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
	//}
	
}
